﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NTQOnlineExam.Services.Infrastructure;
using NTQOnlineExam.Services.Models;

namespace NTQOnlineExam.Services.ReportService.Basket
{
  public class GetUserBasketDTODbCommand : ICommand<BasketDTO>
  {
    private readonly string _username;

    public GetUserBasketDTODbCommand(string username)
    {
      _username = username;
    }

    private const string _sql = @"
Declare @CurrentBasketId uniqueidentifier
Select top 1 @CurrentBasketId = BasketId 
from [dbo].[Basket]
where username = @UserName
order by CreationDate desc

Select BasketId, UserName, CreationDate, Comment,PaymentFee,PaymentFeeTax
from dbo.Basket
where BasketId = @CurrentBasketId

SELECT  [Id], [BasketId],[TermId],[TermName],[Price],[Comment]
FROM    [dbo].[BasketItem]
WHERE   BasketId = @CurrentBasketId
Order By TermId
        ";


    public BasketDTO Execute()
    {
      var connectionString = ConfigurationManager.AppSettings["ConnectionString"];
      using (SqlConnection connection = new SqlConnection(connectionString))
      {
        BasketDTO item = null;
        SqlCommand cmd = new SqlCommand(_sql, connection);
        cmd.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = _username;
        connection.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        try
        {
          while (reader.Read())
          {
            item = new BasketDTO
            {
              BasketId = new Guid(reader["BasketId"].ToString()),
              Comment = reader["Comment"] != DBNull.Value ? reader["Comment"].ToString() : string.Empty
            };
          }
          reader.NextResult();

          if (item != null)
          {
            item.BasketItems = new List<BasketItemDTO>();
            while (reader.Read())
            {
              item.BasketItems.Add(GetItemDto(reader));
            }
          }
        }
        finally
        {
          reader.Close();
        }
        return item;
      }
    }
    private BasketItemDTO GetItemDto(SqlDataReader reader)
    {
      return new BasketItemDTO
      {
        Id =  Guid.Parse(reader["Id"].ToString()),
        TermName = reader["TermName"].ToString(),
        Comment = reader["Comment"] != DBNull.Value ? reader["Comment"].ToString() : string.Empty,
        Price = reader["Price"] != DBNull.Value ? (decimal.Parse(reader["Price"].ToString())) : 0,
        TermId = (int)reader["TermId"]
      };
    }

  }
}